﻿/*
#**************************************************************************
#  openSIS is a student information system for public and non-public 
#  schools from Open Solutions for Education, Inc. web: www.os4ed.com
#
#  openSIS is  web-based, open source, and comes packed with features that 
#  include student demographic info, scheduling, grade book, attendance, 
#  report cards, eligibility, transcripts, parent portal, 
#  student portal and more.   
#
#  Visit the openSIS web site at http://www.opensis.com to learn more.
#  If you have question regarding this system or the license, please send 
#  an email to info@os4ed.com.
#
#  This program is released under the terms of the GNU General Public License  
#  as  published by the Free Software Foundation, version 2 of the License. 
#  See license.txt.
#
#  This program is distributed in the hope that it will be useful,
#  but WITHOUT ANY WARRANTY; without even the implied warranty of
#  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#  GNU General Public License for more details.
#
#  You should have received a copy of the GNU General Public License
#  along with this program.  If not, see <http://www.gnu.org/licenses/>.
#
#**************************************************************************** 

*/

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using OpensisSchoolBusinessLayer;
using OpenSISDataConnection;
using System.Globalization;

namespace OpenSISSchoolDataLayer

{
    class SchoolDL
    {
        public static string server = System.Configuration.ConfigurationManager.AppSettings["appserver"];
      //  public static string server = "MSSQLServer";

        public void SchoolAdd(SchoolBL school)
        {

            if (server == "MSSQLServer")
            {
                DataSet ds = null;

                ds = DataConnection.ConnectionPath.Getdata("exec getMax sch_id,schools");

                String strSchoolAdd = "INSERT into schools(sch_id,sch_short_nm,sch_full_nm,sch_addr1,sch_addr2,sch_city,sch_st,sch_zip,sch_fax,sch_phn,sch_url,sch_ceeb_nbr,sch_type,sch_ed_lvl,sch_po_box_zip,sch_po_box_st,sch_po_box_city,sch_po_box,sch_emer_phn,last_updated,sch_email,sch_principal,sch_base_grading_scale,sch_country,sch_po_box_addr1,sch_po_box_addr2,sch_po_box_country) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + ",N'" + school.ShortName.Replace("'", "''") + "',N'" + school.SchoolName.Replace("'", "''") + "',N'" + school.Address1.Replace("'", "''") + "',N'" + school.Address2.Replace("'", "''") + "', N'" + school.City + "',N'" + school.State + "',N'" + school.Zip + "',N'" + school.Fax + "',N'" + school.Phone + "',N'" + school.Url + "',N'" + school.CeebNum + "',N'" + school.Type + "',N'" + school.Level + "',N'" + school.PostZip + "',N'" + school.PostState + "',N'" + school.PostCity + "',N'" + school.PostBox + "',N'" + school.EmerPhone + "',N'" + school.LastUpdated + "',N'" + school.Email + "',N'" + school.Principal + "',N'" + school.BaseGradeScale + "',N'" + school.Country + "',N'" + school.PostBoxAddress1 + "',N'" + school.PostBoxAddress2 + "',N'" + school.PostBoxCountry + "')";

                DataConnection.ConnectionPath.ExecuteCommand(strSchoolAdd);

            }
            if (server == "MySQL")
            {


                DataSet ds = null;


                ds = DataConnection.ConnectionPath.Getdata("CALL getmax('sch_id','schools')");

                DateTime s = Convert.ToDateTime(school.LastUpdated, DateTimeFormatInfo.InvariantInfo);
                school.LastUpdated = s.ToString("yyyy/MM/dd");





                String strSchoolAdd = "INSERT into schools(sch_id,sch_short_nm,sch_full_nm,sch_addr1,sch_addr2,sch_city,sch_st,sch_zip,sch_fax,sch_phn,sch_url,sch_ceeb_nbr,sch_type,sch_ed_lvl,sch_po_box_zip,sch_po_box_st,sch_po_box_city,sch_po_box,sch_emer_phn,last_updated,sch_email,sch_principal,sch_base_grading_scale,sch_country,sch_po_box_addr1,sch_po_box_addr2,sch_po_box_country) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + ",N'" + school.ShortName.Replace("'", "''") + "',N'" + school.SchoolName.Replace("'", "''") + "',N'" + school.Address1.Replace("'", "''") + "',N'" + school.Address2.Replace("'", "''") + "', N'" + school.City + "',N'" + school.State + "',N'" + school.Zip + "',N'" + school.Fax + "',N'" + school.Phone + "',N'" + school.Url + "',N'" + school.CeebNum + "',N'" + school.Type + "',N'" + school.Level + "',N'" + school.PostZip + "',N'" + school.PostState + "',N'" + school.PostCity + "',N'" + school.PostBox + "',N'" + school.EmerPhone + "',N'" + school.LastUpdated + "',N'" + school.Email + "',N'" + school.Principal + "',N'" + school.BaseGradeScale + "',N'" + school.Country + "',N'" + school.PostBoxAddress1 + "',N'" + school.PostBoxAddress2 + "',N'" + school.PostBoxCountry + "')";

                DataConnection.ConnectionPath.ExecuteCommand(strSchoolAdd);


            }
            else
            {

            }


        }

        public void SchoolEdit(SchoolBL school)
        {
            if (server == "MSSQLServer")
            {
                string strSchoolEdit = "update schools set sch_short_nm= N'" + school.ShortName.Replace("'", "''") + "',sch_full_nm= N'" + school.SchoolName.Replace("'", "''") + "',sch_addr1= N'" + school.Address1.Replace("'", "''") + "',sch_addr2= N'" + school.Address2.Replace("'", "''") + "',sch_city= N'" + school.City + "',sch_st= N'" + school.State + "',sch_zip= N'" + school.Zip + "',sch_fax= N'" + school.Fax + "',sch_phn= N'" + school.Phone + "',sch_url= N'" + school.Url + "',sch_ceeb_nbr= N'" + school.CeebNum + "',sch_type= N'" + school.Type + "',sch_ed_lvl= N'" + school.Level + "',sch_po_box_zip= N'" + school.PostZip + "',sch_po_box_st= N'" + school.PostState + "',sch_po_box_city= N'" + school.PostCity + "',sch_po_box= N'" + school.PostBox + "',sch_emer_phn=N'" + school.EmerPhone + "',last_updated=  N'" + school.LastUpdated + "',sch_email= N'" + school.Email + "',sch_principal= N'" + school.Principal + "',sch_base_grading_scale= N'" + school.BaseGradeScale + "',sch_country= N'" + school.Country + "',sch_po_box_addr1=  N'" + school.PostBoxAddress1 + "',sch_po_box_addr2= N'" + school.PostBoxAddress2 + "',sch_po_box_country= N'" + school.PostBoxCountry + "' where sch_id=" + school.SchoolID + "";

                DataConnection.ConnectionPath.ExecuteCommand(strSchoolEdit);
            }

            if (server == "MySQL")
            {

                DateTime s = Convert.ToDateTime(school.LastUpdated, DateTimeFormatInfo.InvariantInfo);
                school.LastUpdated = s.ToString("yyyy/MM/dd");


                string strSchoolEdit = "update schools set sch_short_nm= N'" + school.ShortName.Replace("'", "''") + "',sch_full_nm= N'" + school.SchoolName.Replace("'", "''") + "',sch_addr1= N'" + school.Address1.Replace("'", "''") + "',sch_addr2= N'" + school.Address2.Replace("'", "''") + "',sch_city= N'" + school.City + "',sch_st= N'" + school.State + "',sch_zip= N'" + school.Zip + "',sch_fax= N'" + school.Fax + "',sch_phn= N'" + school.Phone + "',sch_url= N'" + school.Url + "',sch_ceeb_nbr= N'" + school.CeebNum + "',sch_type= N'" + school.Type + "',sch_ed_lvl= N'" + school.Level + "',sch_po_box_zip= N'" + school.PostZip + "',sch_po_box_st= N'" + school.PostState + "',sch_po_box_city= N'" + school.PostCity + "',sch_po_box= N'" + school.PostBox + "',sch_emer_phn=N'" + school.EmerPhone + "',last_updated=  N'" + school.LastUpdated + "',sch_email= N'" + school.Email + "',sch_principal= N'" + school.Principal + "',sch_base_grading_scale= N'" + school.BaseGradeScale + "',sch_country= N'" + school.Country + "',sch_po_box_addr1=  N'" + school.PostBoxAddress1 + "',sch_po_box_addr2= N'" + school.PostBoxAddress2 + "',sch_po_box_country= N'" + school.PostBoxCountry + "' where sch_id=" + school.SchoolID + "";

                DataConnection.ConnectionPath.ExecuteCommand(strSchoolEdit);
            }
            else
            {
            }

        }

        public System.Data.DataTable SearchSchoolDetails(SchoolBL school)
        {
            DataTable dt = null;
            dt = DataConnection.ConnectionPath.Getdata("select schools.* from schools where sch_full_nm like '%' and sch_short_nm like '%' and sch_type like '%'and sch_city like '%' and sch_state like '%' and sch_zip like '%'").Tables[0];
            return dt;
        }

        //public System.Data.DataTable SchoolDetailsByID(SchoolBL school)
        //{
        //    DataTable dt = null;
        //    dt = DataConnection.ConnectionPath.Getdata("select schools.*,grade_levels.grd_lvl_short_nm as MaxGrade,gl.grd_lvl_short_nm as MinGrade,look_up_data.NAME as State,lud.NAME as PostState,ludCountry.NAME as Country from schools left outer join grade_levels on grade_levels.grd_lvl_id=schools.sch_max_grade_id left outer join  grade_levels as gl on gl.grd_lvl_id=schools.sch_min_grade_id left outer join look_up_data on schools.sch_st=look_up_data.IID and look_up_data.Type=2 left outer join look_up_data as lud on schools.sch_po_box_st=lud.IID and lud.Type=2 left outer join look_up_data as ludCountry on schools.sch_country=ludCountry.IID and ludCountry.TYPE=1 where schools.sch_id='" + school.SchoolID + "'").Tables[0];

        //    if (dt.Rows.Count == 0)
        //    {
        //        dt = DataConnection.ConnectionPath.Getdata("select * from schools where sch_id='" + school.SchoolID + "'").Tables[0];
        //    }

        //    return dt;
        //}





        public System.Data.DataTable SchoolDetailsByID(SchoolBL school)
        {
            DataTable dt = null;
            dt = DataConnection.ConnectionPath.Getdata("select schools.*,look_up_data.NAME as State,lud.NAME as PostState,ludCountry.NAME as Country,ludPostCountry.NAME as PostCountry from schools left outer join look_up_data on schools.sch_st=look_up_data.IID and look_up_data.Type=2 left outer join look_up_data as lud on schools.sch_po_box_st=lud.IID and lud.Type=2 left outer join look_up_data as ludCountry on schools.sch_country=ludCountry.IID and ludCountry.TYPE=1  left outer join look_up_data as ludPostCountry on schools.sch_po_box_country=ludPostCountry.IID and ludPostCountry.TYPE=1 where schools.sch_id='" + school.SchoolID + "'").Tables[0];

            if (dt.Rows.Count == 0)
            {
                dt = DataConnection.ConnectionPath.Getdata("select * from schools where sch_id='" + school.SchoolID + "'").Tables[0];
            }

            return dt;
        }

        public DataSet PopulateGrade(SchoolBL school)
        {
            DataSet ds = null;
            ds = DataConnection.ConnectionPath.Getdata("select * from grade_levels where sch_id='" + school.SchoolID + "'");
            return ds;
        }

        public DataSet PopulateState()
        {
            DataSet ds = null;
            ds = DataConnection.ConnectionPath.Getdata("select * from look_up_data where type=2");
            return ds;
        }

        public DataSet PopulateSchool()
        {
            DataSet ds = null;
            ds = DataConnection.ConnectionPath.Getdata("select * from schools");
            return ds;
        }

        public System.Data.DataTable PopulateMaxSchoolID()
        {
            DataTable dt = null;
            dt = DataConnection.ConnectionPath.Getdata("SELECT max(sch_id) from schools").Tables[0];
            return dt;
        }
        public DataSet PopulateFullYear(SchoolBL school)
        {
            DataSet ds = null;
            ds = DataConnection.ConnectionPath.Getdata("select SMP.schyr_id,SMP.sch_mp_name,SY.schyr_nm from sch_marking_periods SMP inner join school_years SY on SMP.schyr_id=SY.schyr_id inner join year_type YT on SY.years_type_id =YT.years_type_id where SMP.sch_id=" + school.SchoolID + " and YT.year_type_cd ='Y'");
            return ds;
        }

        public DataSet PopulateLevel1(SchoolBL school)
        {
            DataSet ds = null;
            ds = DataConnection.ConnectionPath.Getdata("select SMP.schyr_id,SMP.sch_mp_name from sch_marking_periods SMP inner join school_years SY on SMP.schyr_id=SY.schyr_id inner join year_type YT on SY.years_type_id =YT.years_type_id where SMP.sch_id=" + school.SchoolID + " and SY.schyr_year=" + school.SchyrYear + " and YT.years_type_id=2");
            return ds;
        }

        //***********************(08/07/09)*******************

        public DataSet PopulateCountry()
        {
            DataSet ds = null;
            ds = DataConnection.ConnectionPath.Getdata("select * from look_up_data where type=1");
            return ds;
        }

        //****************(10/07/09)****************************

        public void UpdateMaxGrade(SchoolBL school)
        {

            string strUpdateMaxGrade = "update schools set sch_max_grade_id= N'" + school.MaxGrade + "' where sch_id =" + school.SchoolID + "";

            DataConnection.ConnectionPath.ExecuteCommand(strUpdateMaxGrade);

        }

        public void UpdateMinGrade(SchoolBL school)
        {

            string strUpdateMinGrade = "update schools set sch_min_grade_id= N'" + school.MinGrade + "' where sch_id=" + school.SchoolID + "";

            DataConnection.ConnectionPath.ExecuteCommand(strUpdateMinGrade);

        }
    }
}
